package dbgroup02.dao.db2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import dbgroup02.bean.Baggage;
import dbgroup02.dao.BaggageDao;

public class BaggageDB2Dao implements BaggageDao {

	private JdbcTemplate jdbcTemplate;

	ParameterizedRowMapper<Baggage> getBaggageMapper = new ParameterizedRowMapper<Baggage>() {
		@Override
		public Baggage mapRow(ResultSet resultSet, int arg1)
				throws SQLException {
			Baggage baggage = new Baggage();
			baggage.setIdBaggage(resultSet.getInt(1));
			baggage.setWeight(resultSet.getInt(2));
			baggage.setType(resultSet.getString(3));
			baggage.setIdCheckInInfo(resultSet.getInt(4));
			return baggage;
		}
	};

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public void addBaggage(final Baggage baggage) {
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement("INSERT INTO "
						+ SCHEMA + ".BAGGAGE (WEIGHT, TYPE, CHECKIN_INFO_ID)"
						+ "VALUES (?,?,?)");
				ps.setInt(1, baggage.getWeight());
				ps.setString(2, baggage.getType());
				ps.setInt(3, baggage.getIdCheckInInfo());
				return ps;
			}
		});
	}

	@Override
	@Transactional(readOnly = true, propagation = Propagation.REQUIRED)
	public Baggage getBaggage(int idBaggage) {
		List<Baggage> result = jdbcTemplate.query("SELECT * FROM "
				+ BaggageDao.SCHEMA + ".BAGGAGE WHERE BAGGAGE_ID = ?",
				new Object[] { idBaggage }, getBaggageMapper);

		if (result.size() > 0)
			return result.get(0);
		else
			return null;
	}

	@Override
	@Transactional(readOnly = true, propagation = Propagation.REQUIRED)
	public Baggage getBaggageByCheckInInfo(int idCheckInInfo) {
		List<Baggage> result = jdbcTemplate.query("SELECT * FROM "
				+ BaggageDao.SCHEMA + ".BAGGAGE WHERE CHECKIN_INFO_ID = ?",
				new Object[] { idCheckInInfo }, getBaggageMapper);

		if (result.size() > 0)
			return result.get(0);
		else
			return null;
	}

}
